package com.rykj.qxj.server.services.impl;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.rykj.qxj.server.dao.DatabaseSourceMapper;
import com.rykj.qxj.server.model.entity.DatabaseSource;
import com.rykj.qxj.server.services.IDatabaseSourceService;
import com.rykj.qxj.server.util.DES;
import com.rykj.qxj.server.util.JdbcClient;
import com.rykj.qxj.server.util.object.StringUtils;
import lombok.extern.log4j.Log4j;
import org.springframework.stereotype.Service;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

@Log4j
@Service("databaseSourceService")
public class DatabaseSourceServiceImpl implements IDatabaseSourceService {
    @Resource
    private DatabaseSourceMapper sourceMapper;

    private final   String mysqlDriver = "com.mysql.jdbc.Driver";
    @Override
    public String saveDatabaseSource(DatabaseSource databaseSource) throws Exception{
        sourceMapper.saveDatabaseSource(databaseSource);
        String errormsg = null;
        if(!isExistDatabase(databaseSource)){
            errormsg = createDatabase(databaseSource);
        }
        if(StringUtils.isNotEmpty(errormsg)){
            return errormsg;
        }
        return errormsg;
    }
    public boolean isExistDatabase(DatabaseSource databaseSource) throws SQLException, ClassNotFoundException {
        String url = "jdbc:mysql://"+databaseSource.getDatabaseIP()+":"+databaseSource.getPort()+"/";
        String driver = "com.mysql.jdbc.Driver";
        String password = new DES().decode(databaseSource.getPassword());;
        JdbcClient jdbcClient = new JdbcClient(url,databaseSource.getUserName(),password,driver);
        String sql = "SELECT COUNT(*) sm FROM information_schema.schemata WHERE schema_name=\"" + databaseSource.getDatabaseName() + "\"";
        List<Map<String, Object>> list = jdbcClient.query(sql);
        if(list!=null){
            int sm = list.get(0).get("sm")!=null?Integer.parseInt(list.get(0).get("sm").toString()):0;
            if (list.size()>0&&sm>0) {
                return true;
            }
        }
        return false;
    }
    /**
     * 检测ip下数据库是否存在
     * @param databaseName
     * @param databaseIP
     * @return
     */
    @Override
    public boolean databaseIfExist(String databaseName, String databaseIP) {
       boolean flag = false;
       Map<String,Object> paraMap = new HashMap<>();
       paraMap.put("databaseName",databaseName);
       paraMap.put("databaseIP",databaseIP);
       DatabaseSource databaseSource = sourceMapper.databaseIfExist(paraMap);
       //不存在
       if (databaseSource==null){
           flag = true;
       }
       return  flag;
    }
    @Override
    public void updateDatabaseSource(DatabaseSource databaseSource) {
        sourceMapper.updateDatabaseSource(databaseSource);
    }
    @Override
    public void deleteDatabaseSourceById(Integer baseSourcId) {
        sourceMapper.deleteDatabaseSourceById(baseSourcId);
    }
    @Override
    public PageInfo getDatabaseSourceList(Integer pageNum, Integer pageSize, String classification, String databaseName,String databaseCNName,
                                          String directory,Integer appId,String databaseType) {
        PageHelper.startPage(pageNum,pageSize);
        Map<String,Object> paraMap = new HashMap<>();
        if("%".equals(databaseName)){
            databaseName ="\\%";
        }
        if("%".equals(databaseCNName)){
            databaseCNName ="\\%";
        }
        paraMap.put("classification",classification);
        paraMap.put("databaseName",databaseName);
        paraMap.put("directory",directory);
        paraMap.put("appId",appId);
        paraMap.put("databaseCNName",databaseCNName);
        paraMap.put("databaseType",databaseType);
        List<DatabaseSource> databaseSources =  sourceMapper.getDatabaseSourceList(paraMap);
        PageInfo pageInfo = new PageInfo(databaseSources);
        return  pageInfo;
    }
    @Override
    public DatabaseSource getDatabaseSourceDetails(Integer id) {
        return  sourceMapper.getDatabaseSourceDetails(id);
    }
    @Override
    public String createDatabase(DatabaseSource databaseSource) throws  Exception{
        String newUrl = "jdbc:mysql://"+databaseSource.getDatabaseIP()+":"+databaseSource.getPort()+"/";
        String username = databaseSource.getUserName();
        String password = databaseSource.getPassword();
        password =new DES().decode(password);
        Connection newConn = null;
        String database = databaseSource.getDatabaseName();
        try {
            Class.forName(mysqlDriver);
        } catch (ClassNotFoundException e) {
            log.error("mysqlDriver找不到",e);
            throw  new Exception("mysqlDriver找不到");
        }
        try {
            DriverManager.setLoginTimeout(5);
            newConn = DriverManager.getConnection(newUrl ,username, password);
            if (newConn != null) {
                String databaseSql = " create database `" + database+"` DEFAULT CHARSET utf8 COLLATE utf8_general_ci";
                Statement smt = newConn.createStatement();
                System.out.println(databaseSource.getDatabaseIP()+":创建连接成功！");
                smt.executeUpdate(databaseSql);
                newConn = DriverManager.getConnection(newUrl +databaseSource.getDatabaseName(),username, password);
            }else {
                throw  new Exception("创建连接失败");
            }
        } catch (SQLException sqle) {
             log.error("数据库已存在",sqle);
            throw  new Exception("数据库已存在");
        }finally {
            if(newConn!=null){
                try {
                    newConn.close();
                } catch (SQLException e) {
                   log.error("关闭数据库连接失败",e);
                    throw  new Exception("关闭数据库连接失败");
                }
            }
        }
        return "";
    }

    /**
     * 检测连接是否成功
     * @param databaseIp
     * @param userName
     * @param password
     * @return
     */
    @Override
    public String checkConnect(String databaseIp,String prot, String userName, String password) {
        password = new DES().decode(password);
        String errorMsg = "";
        String checkUrl = "jdbc:mysql://"+databaseIp+":"+prot+"/";
        Connection conn = null;
        try {
            Class.forName(mysqlDriver);
        } catch (ClassNotFoundException e) {
            log.error("mysqlDriver找不到",e);
            return "未安装mysql驱动连接程序";
        }
        try {
            DriverManager.setLoginTimeout(5);
            conn = DriverManager.getConnection(checkUrl ,userName, password);
            if (conn == null) {
                errorMsg="连接失败";
            }
        } catch (SQLException sqle) {
            log.error("创建连接超时",sqle);
            errorMsg="连接超时";
        }
        finally {
            if(conn!=null){
                try {
                    conn.close();
                } catch (SQLException e) {
                    log.error("检测连接，关闭数据库连接失败",e);
                    errorMsg="关闭数据库连接异常";
                }
            }
        }
        return  errorMsg;
    }

    @Override
    public DatabaseSource getSzqxReadDataSource() {
       return sourceMapper.getSzqxReadDataSource();
    }
}
